package com.orhon.smartcampus.modules.student.fetcher;

import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Component;

import com.orhon.smartcampus.auth.JwtUtils;
import com.orhon.smartcampus.modules.user.dao.TokenService;

import graphql.schema.DataFetcher;
import graphql.schema.DataFetchingEnvironment;

@Component
public class StatisticsFetcher implements DataFetcher  {
	
	@Autowired
	private TokenService tokenService;
	
	@Autowired
    private HibernateTemplate hibernateTemplate;

	@Override
	public Object get(DataFetchingEnvironment env) throws Exception {
		String token = tokenService.getSystemToken();
		Long school_id = JwtUtils.getSchoolId(token);
		int statics = Integer.parseInt(env.getArgument("static").toString());
		String hql="";
//		if(statics==1) {
//			hql+="SELECT count(*) AS sl,substring(trim(BOTH '' FROM cast(JSON_EXTRACT(c.NAME,'$.zh') AS CHAR)),1,2) AS jie FROM (SELECT t.go_eclass,t.student_id FROM (SELECT*FROM student_eclass_records ORDER BY id DESC LIMIT 10000) AS t GROUP BY t.student_id) a,student_eclass b,student_arrives c,student_information d WHERE a.go_eclass=b.id AND arrives_id=c.id AND c.school_id="+school_id+" AND a.student_id=d.id AND d.deleted_at IS NULL GROUP BY c.period_id";
//		}
//		if(statics==2) {
//			hql+= "SELECT CONCAT(ROUND(count(CASE WHEN sex='gender_1' THEN sex END)/count(*)*100,2),'%') AS nan,CONCAT(ROUND(count(CASE WHEN sex='gender_2' THEN sex END)/count(*)*100,2),'%') AS nv,count(*) AS zsl FROM student_information WHERE 1=1 AND school_id=1";
//		}
		// 教师男女比例
		if (statics==1) {
			hql+= "SELECT CASE gender WHEN '0' THEN '男' WHEN '1' THEN '女' END AS name,\r\n" + 
			"count(*) value FROM teacher_information WHERE deleted_at IS NULL and (gender = '0' or gender = '1') and school_id =" +school_id+ " GROUP BY name";
		}
		
		// 教师年龄段比例
		if (statics==2) {
			hql+= "select\r\n" + 
					"        case\r\n" + 
					"              when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())>=20 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())<=30\r\n" + 
					"                  then '20-30'\r\n" + 
					"              when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())>=30 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())<=40\r\n" + 
					"                  then '30-40'\r\n" + 
					"              when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())>=40 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())<=50\r\n" + 
					"                  then '40-50'\r\n" + 
					"              when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())>=50 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())<=60\r\n" + 
					"                  then '50-60'\r\n" + 
					"              when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(idcard,7,8),'%Y%m%d'),sysdate())>=60\r\n" + 
					"                  then '60以上' \r\n" + 
					"									END name,count(*) value                    \r\n" + 
					"             from teacher_information WHERE\r\n" + 
					"	deleted_at IS NULL and school_id = " +school_id+ " group by name";
		}
		
		// 教师民族比例
		if (statics==3) {
			hql+= "SELECT CASE nation  WHEN '01' THEN '汉族' \r\n" + 
					"										WHEN '02' THEN '蒙古族' \r\n" + 
					"										WHEN '03' THEN '回族'\r\n" + 
					"										else '其他民族'  \r\n" + 
					"END AS name,\r\n" + 
					"count(*) value FROM teacher_information WHERE deleted_at IS NULL and school_id = " +school_id+ "  GROUP BY name";
		}
		
		// 教师学历比例
		if (statics==4) {
			hql+= "SELECT\r\n" + 
					"	d.dictionary_name AS name,\r\n" + 
					"	( SELECT count( id ) FROM teacher_information WHERE education = d.value_code AND school_id = "+school_id+" ) \r\n" + 
					"value\r\n" + 
					"FROM\r\n" + 
					"	base_dictionary d \r\n" + 
					"WHERE\r\n" + 
					"	d.dictionary_code = 'education'";
		}
		
		// 教师职称数量比例
		if (statics==5) {
			hql+= "SELECT\r\n" + 
					"	d.dictionary_name AS name,\r\n" + 
					"	( SELECT count( id ) FROM teacher_information WHERE jobtitle = d.value_code AND school_id = "+school_id+" ) \r\n" + 
					"value\r\n" + 
					"FROM\r\n" + 
					"	base_dictionary d \r\n" + 
					"WHERE\r\n" + 
					"	d.dictionary_code = 'jobtitle'";
		}
       // 统计高中和初中的人数 和总人数
		if (statics==6) {
			hql+= "SELECT\n" +
					"count(case when  a.period_slug = \"middle\" or a.period_slug = \"high\"  then 1 end) as all_num,\n" +
					"count(case when  a.period_slug = \"high\" then 1 end) as high_num,\n" +
					"count(case when  a.period_slug = \"middle\" then 1 end) as middle_num FROM\n" +
					"base_periods a,\n" +
					"student_learninfo b,\n" +
					"student_arrives c\n" +
					"where\n" +
					"b.arrive_id = c.id AND\n" +
					"c.period_id = a.id AND\n" +
					"b.at_school=1 AND\n" +
					"b.school_id = "+school_id+"\n" +
					"AND\n" +
					"b.deleted_at is null";
		}
		//统计十年内每年学生入学人数
		if (statics==7) {
			hql+= "SELECT\n" +
					"count(distinct student_id)  as  student_num,left(start_year,4) as year from student_learninfo\n" +
					"where start_year > (YEAR(CURDATE())-10) AND\n" +
					"school_id = "+school_id+"\n" +
					"AND deleted_at is NULL\n" +
					"GROUP BY start_year";
		}
		//统计十年蒙古族和汉族人数比例变化
		if (statics==8) {
			hql+= "SELECT\n" +
					"count( CASE WHEN a.nation = 'nation_01' THEN a.nation END ) AS han_nation,\n" +
					"count( CASE WHEN a.nation = 'nation_02' THEN a.nation END ) AS meng_nation,\n" +
					"left(b.start_year,4) as year\n" +
					"FROM\n" +
					"student_information a,\n" +
					"student_learninfo b " +
					"where \n " +
					"a.id = b.student_id\n" +
					"AND b.at_school = 1\n" +
					"AND a.school_id ="+school_id+"\n" +
					"AND b.start_year is NOT NULL\n" +
					"AND b.start_year > (YEAR(CURDATE())-10)\n" +
					"AND a.deleted_at is NULL \n" +
					"GROUP BY b.start_year";
		}
		//统计十年男生和女生人数比例变化
		if (statics==9) {
			hql+= "SELECT\n" +
					"count( CASE WHEN a.sex = 'gender_1' THEN a.sex END ) AS nan_num,\n" +
					"count( CASE WHEN a.sex = 'gender_2' THEN a.sex END ) AS nv_num,\n" +
					"left(b.start_year,4) as year\n" +
					"FROM\n" +
					"student_information a,\n" +
					"student_learninfo b " +
					"where \n" +
					"a.id = b.student_id\n" +
					"AND b.at_school = 1\n" +
					"AND a.school_id ="+school_id+"\n" +
					"AND b.start_year is NOT NULL\n" +
					"AND b.start_year > (YEAR(CURDATE())-10)\n" +
					"AND a.deleted_at is NULL \n" +
					"GROUP BY b.start_year";
		}
		//统计在校各年级人数
		if (statics==10) {
			hql+= "SELECT f.grade_slug as grade_name,count(*) as student_num,\n" +
					"CONCAT(ROUND(count(*)/(SELECT count(*) from student_information)*100,2),'%') as grade_scale\n" +
					"FROM\n" +
					"(SELECT\n" +
					"a.id,d.grade_slug,a.school_id\n" +
					"FROM\n" +
					"student_information a\n" +
					"LEFT JOIN\n" +
					"student_learninfo b\n" +
					"ON a.id = b.student_id\n" +
					"LEFT JOIN student_arrives c\n" +
					"ON b.arrive_id = c.id\n" +
					"LEFT JOIN base_grades d\n" +
					"ON c.grade_id = d.id\n" +
					"WHERE \n" +
					"a.school_id="+school_id+"\n" +
					"AND b.at_school=1\n" +
					"AND a.deleted_at is null ) as f where " +
					"f.grade_slug is not null\n" +
					"GROUP BY f.grade_slug";
		}
		//统计在校男女比例以及人数和总数
		if (statics==11) {
			hql+= "SELECT\n" +
					"count( CASE WHEN a.sex = 'gender_1' THEN a.sex END ) AS male_count,\n" +
					"count( CASE WHEN a.sex = 'gender_2' THEN a.sex END ) AS female_count,\n" +
					"CONCAT(ROUND(count( CASE WHEN a.sex = 'gender_1' THEN a.sex END )/count(*)*100,2),'%') as male_scale,\n" +
					"CONCAT(ROUND(count( CASE WHEN a.sex = 'gender_2' THEN a.sex END )/count(*)*100,2),'%') as female_scale,\n" +
					"count( * ) AS all_num\n" +
					"FROM\n" +
					"student_information a,\n" +
					"student_learninfo b\n" +
					"where \n " +
					"a.id = b.student_id\n" +
					"AND b.at_school = 1\n" +
					"AND a.school_id = "+school_id+"\n" +
					"AND a.deleted_at is NULL";
		}
		//统计在校蒙族和汉族以及其他名族人数以及比例 和总人数
		if (statics==12) {
			hql+= "SELECT\n" +
					"count( CASE WHEN a.nation = 'nation_01' THEN a.nation END ) AS han_nation,\n" +
					"count( CASE WHEN a.nation = 'nation_02' THEN a.nation END ) AS meng_nation,\n" +
					"(count( * )-count( CASE WHEN a.nation = 'nation_01' or a.nation = 'nation_02' THEN a.nation END )) AS other_nation,\n" +
					"CONCAT(ROUND(count( CASE WHEN a.nation = 'nation_01' THEN a.nation END )/count(*)*100,2),'%') as han_nation_scale,\n" +
					"CONCAT(ROUND(count( CASE WHEN a.nation = 'nation_02' THEN a.nation END )/count(*)*100,2),'%') as meng_nation_scale,\n" +
					"CONCAT(ROUND((count( * )-count( CASE WHEN a.nation = 'nation_01' or a.nation = 'nation_02' THEN a.nation END ))/count(*)*100,2),'%') as other_nation_scale,\n" +
					"count( * ) AS all_num\n" +
					"FROM\n" +
					"student_information a,\n" +
					"student_learninfo b\n" +
					"where \n" +
					"a.id = b.student_id\n" +
					"AND b.at_school = 1\n" +
					"AND a.school_id = "+school_id+"\n" +
					"AND a.deleted_at is NULL";
		}
		//统计在校生年龄段
		if (statics==13) {
			hql+= "SELECT t.age_range as\n" +
					"age_range,COUNT(1) as student_num FROM (SELECT CASE\n" +
					"WHEN ROUND(DATEDIFF(CURDATE(),\n" +
					"a.birthday)/365.2422) > 0 AND\n" +
					"ROUND(DATEDIFF(CURDATE(),\n" +
					"a.birthday)/365.2422) <15 THEN '15岁以下'\n" +
					"WHEN ROUND(DATEDIFF(CURDATE(),\n" +
					"a.birthday)/365.2422) >= 15 AND\n" +
					"ROUND(DATEDIFF(CURDATE(),\n" +
					"a.birthday)/365.2422) <20 THEN '15-20岁'\n" +
					"WHEN\n" +
					"ROUND(DATEDIFF(CURDATE(), a.birthday)/365.2422) >= 20  THEN\n" +
					"'20以上' ELSE\n" +
					"'其他' END as age_range FROM student_information a,student_learninfo b\n" +
					"where \n" +
					"a.id=b.student_id AND a.school_id="+school_id+" AND b.at_school=1)\n" +
					"as t\n" +
					"GROUP BY\n" +
					"t.age_range;";
		}

		SessionFactory sessionFactory = hibernateTemplate.getSessionFactory();
		Session openSession = sessionFactory.openSession();
		SQLQuery query = (SQLQuery) openSession.createSQLQuery(hql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map> list = query.list();
		openSession.close();
		return list;
	}

}
